package br.com.localfloripa.dao;

import java.util.List;

import javax.inject.Named;
import javax.persistence.Query;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.annotation.Scope;

import br.com.localfloripa.entities.Anuncio;

@Named("anuncioDao")
@Scope("singleton")
public class AnuncioDao extends DaoManager<Anuncio> {
	
	private static final Logger log = LoggerFactory.getLogger(AnuncioDao.class);
	
	public Anuncio findById(Long idAnuncio) throws Exception {
		
		StringBuilder builder = new StringBuilder();
		builder.append("SELECT a FROM Anuncio a ");
		builder.append("JOIN FETCH a.contato c ");
		builder.append("JOIN FETCH c.endereco e ");
		builder.append("LEFT JOIN FETCH a.album f ");
		builder.append("JOIN FETCH a.ramo r ");
		builder.append("WHERE a.id = :idAnuncio ");
		builder.append("AND a.ativo IS TRUE");
		Query query = em.createQuery(builder.toString());
		query.setParameter("idAnuncio", idAnuncio);
		
		return (Anuncio) query.getSingleResult();
	}
	
	public List<Anuncio> listLancamentos(Integer rows) throws Exception {
		
		StringBuilder builder = new StringBuilder();
		builder.append("SELECT a FROM Anuncio a JOIN FETCH a.ramo r ");
		builder.append("WHERE a.lancamento IS TRUE ");
		builder.append("AND a.ativo IS TRUE ");
		builder.append("ORDER BY RANDOM()");
				
		Query query = em.createQuery(builder.toString());
		query.setMaxResults(rows);
		
		return query.getResultList();
	}
	
	public List<Anuncio> listLancamentosLazy(int first, int pageSize) throws Exception {
		
		StringBuilder builder = new StringBuilder();
		builder.append("SELECT a FROM Anuncio a ");
		builder.append("JOIN FETCH a.ramo r ");
		builder.append("JOIN FETCH a.contato c ");
		builder.append("JOIN FETCH c.endereco e ");
		builder.append("WHERE a.lancamento IS TRUE ");
		builder.append("AND a.ativo IS TRUE ");
		builder.append("AND r.ativo IS TRUE ");
		builder.append("AND c.ativo IS TRUE ");
		builder.append("ORDER BY RANDOM()");
		
		Query query = em.createQuery(builder.toString());
		query.setMaxResults(pageSize);
		query.setFirstResult(first);
		
		return query.getResultList();
	}
	
	public List<Anuncio> listDestaques(Integer rows) throws Exception {
		
		StringBuilder builder = new StringBuilder();
		builder.append("SELECT a FROM Anuncio a JOIN FETCH a.ramo r ");
		builder.append("WHERE a.lancamento IS NOT TRUE ");
		builder.append("AND a.ativo IS TRUE ");
		builder.append("ORDER BY RANDOM()");
		
		Query query = em.createQuery(builder.toString());
		query.setMaxResults(rows);
		
		return query.getResultList();		
	}
	
	public List<Anuncio> listLancamentosByIdUsuario(Integer rows, Long idUsuario) throws Exception {
		
		StringBuilder builder = new StringBuilder();
		builder.append("SELECT a FROM Anuncio a JOIN FETCH a.ramo r JOIN FETCH a.cliente c JOIN FETCH c.usuario u ");
		builder.append("WHERE a.lancamento IS TRUE ");
		builder.append("AND a.ativo IS TRUE ");
		builder.append("AND u.id = :idUsuario ");
		builder.append("ORDER BY RANDOM()");
		
		Query query = em.createQuery(builder.toString());
		query.setParameter("idUsuario", idUsuario);
		query.setMaxResults(rows);
		
		return query.getResultList();		
	}
	
	public List<Anuncio> listLancamentosByIdUsuarioLazy(int first, int pageSize, Long idUsuario) throws Exception {
		
		StringBuilder builder = new StringBuilder();
		builder.append("SELECT a FROM Anuncio a ");
		builder.append("JOIN FETCH a.cliente c ");
		builder.append("JOIN FETCH c.usuario u ");
		builder.append("JOIN FETCH a.ramo r ");
		builder.append("JOIN FETCH a.contato co ");
		builder.append("JOIN FETCH co.endereco e ");
		builder.append("WHERE a.lancamento IS TRUE ");
		builder.append("AND a.ativo IS TRUE ");
		builder.append("AND r.ativo IS TRUE ");
		builder.append("AND c.ativo IS TRUE ");
		builder.append("AND u.ativo IS TRUE ");
		builder.append("AND co.ativo IS TRUE ");
		builder.append("AND u.id = :idUsuario ");
		builder.append("ORDER BY RANDOM()");
		
		Query query = em.createQuery(builder.toString());
		query.setParameter("idUsuario", idUsuario);
		query.setMaxResults(pageSize);
		query.setFirstResult(first);
		
		return query.getResultList();		
	}
	
	public List<Anuncio> listDestaquesByIdUsuario(Integer rows, Long idUsuario) throws Exception {
		
		StringBuilder builder = new StringBuilder();
		builder.append("SELECT a FROM Anuncio a JOIN FETCH a.ramo r JOIN FETCH a.cliente c JOIN FETCH c.usuario u ");
		builder.append("WHERE a.lancamento IS NOT TRUE ");
		builder.append("AND a.ativo IS TRUE ");
		builder.append("AND u.id = :idUsuario ");
		builder.append("ORDER BY RANDOM()");
		
		Query query = em.createQuery(builder.toString());
		query.setMaxResults(rows);
		
		return query.getResultList();		
	}
	
	public Long countLancamentos() throws Exception {
		
		StringBuilder builder = new StringBuilder();
		builder.append("SELECT COUNT(a) FROM Anuncio a ");
		builder.append("WHERE a.lancamento IS TRUE ");
		builder.append("AND a.ativo IS TRUE");
		
		Query query = em.createQuery(builder.toString());
		
		return (Long) query.getSingleResult();				
	}
	
	public Long countLancamentosByIdUsuario(Long idUsuario) throws Exception {
		
		StringBuilder builder = new StringBuilder();
		builder.append("SELECT a FROM Anuncio a JOIN FETCH a.cliente c JOIN FETCH c.usuario u ");
		builder.append("WHERE a.lancamento IS TRUE ");
		builder.append("AND a.ativo IS TRUE ");
		builder.append("AND u.id = :idUsuario");
		
		Query query = em.createQuery(builder.toString());
		query.setParameter("idUsuario", idUsuario);
		
		return (Long) query.getSingleResult();
	}
}
